/** 增加年龄 */
alter table bcode add age int;

/** 初始化年龄 */
update bcode set age=2012-CONVERT(varchar(4), bird, 120);

/** 增加学位 */
alter table bcode_education add xuewei varchar(256);

/** 创建触发器，单独执行 */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[updateBcodeHostel] ON [dbo].[hr_hostel_bcode]
   AFTER INSERT, DELETE
AS 
BEGIN
	declare @bcode varchar(50) --插入的员工编号
	declare @bedCode varchar(50) --床号
	
	IF EXISTS(SELECT 1 FROM inserted)
	begin
		select @bcode = bcode, @bedCode = code from inserted
	end
	else
	begin
		select @bcode = bcode, @bedCode = '' from deleted
	end
	
	update bcode_job set hostelno = @bedCode where bcode = @bcode
END
GO

/** 更新现有的宿舍号
 */
update bcode_job set hostelno = code from hr_hostel_bcode where bcode_job.bcode = hr_hostel_bcode.bcode;

/** 增加两个菜单，衣柜和宿舍列表 */
insert menu(menucode, menuname, command) values('A0010012', '衣柜列表查询', 'WardrodeList');
insert menu(menucode, menuname, command) values('A0010016', '宿舍列表查询', 'RptHostel');

/** 修改视力v_schedule，防止在继承上月的排班中出现排班天数超出实际天数的情况(第二次才加的SQL) */
alter view v_schedule as
SELECT     depCode, bcode, DATEADD(day, 0, ym) AS ymd, r1 AS bc
FROM         dbo.hr_Attendance_schedule
UNION
SELECT     depCode, bcode, DATEADD(day, 1, ym) AS ymd, r2
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 2, ym) AS ymd, r3
FROM         dbo.hr_Attendance_schedule AS ASbc
UNION
SELECT     depCode, bcode, DATEADD(day, 3, ym) AS ymd, r4
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 4, ym) AS ymd, r5
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 5, ym) AS ymd, r6
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 6, ym) AS ymd, r7
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 7, ym) AS ymd, r8
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 8, ym) AS ymd, r9
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 9, ym) AS ymd, r10
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 10, ym) AS ymd, r11
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 11, ym) AS ymd, r12
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 12, ym) AS ymd, r13
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 13, ym) AS ymd, r14
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 14, ym) AS ymd, r15
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 15, ym) AS ymd, r16
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 16, ym) AS ymd, r17
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 17, ym) AS ymd, r18
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 18, ym) AS ymd, r19
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 19, ym) AS ymd, r20
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 20, ym) AS ymd, r21
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 21, ym) AS ymd, r22
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 22, ym) AS ymd, r23
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 23, ym) AS ymd, r24
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 24, ym) AS ymd, r25
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 25, ym) AS ymd, r26
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 26, ym) AS ymd, r27
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 27, ym) AS ymd, r28
FROM         dbo.hr_Attendance_schedule AS bc
UNION
SELECT     depCode, bcode, DATEADD(day, 28, ym) AS ymd, r29
FROM         dbo.hr_Attendance_schedule AS bc
WHERE     (ISNULL(r29, '') <> '') and day(dateadd(day, -1, DATEADD(MONTH, 1, ym))) >= 29
UNION
SELECT     depCode, bcode, DATEADD(day, 29, ym) AS ymd, r30
FROM         dbo.hr_Attendance_schedule AS bc
WHERE     (ISNULL(r30, '') <> '') and day(dateadd(day, -1, DATEADD(MONTH, 1, ym))) >= 30
UNION
SELECT     depCode, bcode, DATEADD(day, 30, ym) AS ymd, r31
FROM         dbo.hr_Attendance_schedule AS bc
WHERE     (ISNULL(r31, '') <> '') and day(dateadd(day, -1, DATEADD(MONTH, 1, ym))) >= 31;

/** 增加学位设置菜单 */
insert menu(menucode, menuname, command, system) values('A09900050095', '学位设置', 'HR_Degree_Setting', 'manager.html')
/** 更新现有雇员的宿舍号 */
update bcode_job set hostelno=code from hr_hostel_bcode where bcode_job.bcode = hr_hostel_bcode.bcode and hr_hostel_bcode.leavedate is null;  --先查询，再update
/** 增加加班补休审核的权限 */
insert business(busicode, businame) values(8020, '加班补休审核');

/** 新的问题反馈的SQL */
insert dictionary(code, name, type) values('ZL001', '教育程度', 'hr_zhili');
insert dictionary(code, name, type) values('ZL002', '资格', 'hr_zhili');
insert dictionary(code, name, type) values('ZL003', '职称', 'hr_zhili');

insert menu(menucode, menuname, command, system) 
values('A0010055', '雇员资历查询', 'SearchZhiLi', 'manager.html');